package cn.edu.cqut.test0509;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test1 {
	public static void main(String[] args) {
		//1、装载驱动
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
		//2、连接数据库
		// 定义连接URL
		String url = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
		// 数据库用户和密码
		String user = "root";
		String password = "1234";

		try {
			//3、建立连接
			Connection conn = DriverManager.getConnection(url, user, password);
			try {
				//4、操作数据库：建立Statement（增加、删除、修改、查找）...
				//5、查找所有操作
				Statement sql = conn.createStatement();
				ResultSet rs = sql.executeQuery("SELECT * FROM stu_info");
				while(rs.next())
				{
					String id = rs.getString(1);
					String name = rs.getString(2);
					int age = rs.getInt(3);
					System.out.println("id="+id+",name="+name+",age="+age);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			//6、删除学号为12303070101同学（陈小梅）
			Statement sql = conn.createStatement();
			sql.execute("delete from stu_info where id='12303070101'");
			//7、将学号为12303070102同学的年龄修改为30岁（卢杨才）
//			Statement sql1 = conn.createStatement();
			//Update table_name Set column_name = new_value Where column_name = some_value
			sql.execute("update stu_info set age=30 where id='12303070102'");
			//8、在表中新增个学生信息如下：12303070104、马六、23（刘浩然）
			sql.execute("INSERT INTO stu_info(id, name,age) VALUES('12303070104', '马六',23)");
			// 关闭连接
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
